<?php
//No direct access
defined('_JEXEC') or die('Restricted access');

jimport('joomla.application.component.model');

class OwnserviceModelTransaction extends JModel
{
	/**
	 * Transaction data
	 *
	 * @var object
	 */
	var $_data = null;

	/**
	 * Constructor
	 *
	 * @since 1.5
	*/
	function __construct(){
		parent::__construct();

		$id = JRequest::getVar('id');
		$this->setId((int)$id);
	}

	/**
	 * Method to set the transaction id
	 *
	 * @access	public
	 * @param	int	transaction ID number
	 */
	function setId($id) {
		// Set new transaction ID and wipe data
		$this->_id		= $id;
		$this->_data	= null;
	}

	/**
	 * Overridden get method to get properties transaction the data
	 *
	 * @access	public
	 * @param	string	$property	The name of the property
	 * @param	mixed	$value		The value of the property to set
	 * @return 	mixed 				The value of the property
	 * @since	1.0
	 */
	function get($property, $default=null) {
		/*
		* Load the Transaction data
		*/
		if ($this->_loadData()) {
			if(isset($this->_data->$property)) {
				return $this->_data->$property;
			}
		}
		return $default;
	}

	/**
	 * Method to get bank account data
	 *
	 * @access public
	 * @return object
	*/
	function &getData(){
		
		/*
		* Load the Transaction data
		*/
		if( $this->_loadData() ) {
			
			$query =' SELECT recei.firstname ,recei.lastname '
					.' FROM #__ownservice_transactions AS t ' 
					.' LEFT JOIN #__ownservice_users AS recei ON recei.user_id=t.transfer_id '
					.' WHERE t.id='.$this->_id;
			
			$this->_db->setQuery($query);
			$row = $this->_db->loadObject();
	
			if($row)
			{
				$this->_data->firstname = $row->firstname;
				$this->_data->lastname	= $row->lastname;
			}

			$branch	= $this->_getBranch($this->_data->user_id);
			if(is_object($branch))
			{
				$this->_data->branchname	= $branch->name;
				$this->_data->b_code		= $branch->code;
				$this->_data->abn			= $branch->abn;
				$this->_data->b_address		= $branch->address;
				$this->_data->b_city		= $branch->city;
				$this->_data->b_state		= $branch->state;
				$this->_data->b_country		= $branch->country;
				$this->_data->b_postcode	= $branch->postcode;
				$this->_data->b_email		= $branch->email;
				$this->_data->advertising	= $branch->advertising;
			}

		} else {
			$row 			= new stdClass();
			$row->id		= 0;
			$row->user_id	= null;
			$row->reason	= 'Gift';
			$this->_data	= $row;
		}

		return $this->_data;
	}

	/**
	 * Method to load required data
	 *
	 * @access	private
	 * @return	array
	 * @since	1.0
	*/
	function _loadData() {

		//Lets load the transaction if it doesn't already exist
		if(empty($this->_data)) {

			$user = & JFactory::getUser();
			$ownuser = Ownservice::getUser();

			//get vars
			$gid		= $user->gid;
			$level		= $ownuser->get('level_id');

			$where = array();
			$where[] = 't.id='.$this->_id;

			if($gid < 25)
			{
				$canView	= Ownservice::checkAccess('transaction','view','transaction','all');
				$canViewOwn = Ownservice::checkAccess('transaction','view','transaction','own'); 

				if($canViewOwn) {
					$where[] = 't.user_id ='.$user->get('id');
				}

				if($canView) {
					if( ($level==2 ) || ($level==4 ) ) {
						$where[] = 'p.branch_id ='.$ownuser->get('branch_id');
					}else if($level == 3){
						$where[] = 't.transfer_id ='.$user->get('id');
					}
				}
			}

			$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

			$query = ' SELECT t.*, u.name, u.username, cu.username AS cusername, '
					.' p.id AS memberid, p.branch_id, p.nickname, p.birthday, p.phone_no AS p_phone_no, p.address AS p_address, p.id_type , p.id_number,  p.expire_date, p.issuer, u.email AS p_email, b.advertising, '
					.' p.city AS p_city,p.state AS p_state,p.postcode AS p_postcode, p.country AS p_country,p.occupation, b.name AS branchname, b.code AS b_code, b.abn, '
					.' b.address AS b_address, b.city AS b_city, b.state AS b_state, b.country AS b_country, b.postcode AS b_postcode, b.email AS b_email, '
					.' b.phone AS b_phone , a.id AS a_id, a.account_name, a.account_no ,a.bank_name, a.bank_other, a.account_type, a.branch_name, a.address AS a_address, a.city AS a_city, a.state AS a_state, a.postcode AS a_postcode,'
					.' a.country AS a_country, a.phone_no AS a_phone_no, a.branch_city, a.birthday AS a_birthday, c.iscore, tb.code AS tb_code '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__users AS cu ON cu.id = t.created_by'
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_branches AS tb ON tb.id = t.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account and a.user_id=u.id'
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id=t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
					.$where;

			$this->_db->setQuery($query);
			$this->_data = $this->_db->loadObject();

			return (boolean) $this->_data;
		}

		return true;
	}

	function store($data){

		//Check the request forgeries
		JRequest::checkToken() or jexit('Invalid Token');
		
		//Initialise variables
		$user		=& JFactory::getUser();
		$config		=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
		
		$nullDate	=$this->_db->getNullDate();
		
		//get Table
		$row		=& $this->getTable( 'ownservice_transaction','' );
		
		if(!$row->bind($data)) {
			$this->setError( $this->_db->getErrorMsg() );
			return false;
		}

		// sanitise id field
		$row->id		= (int) $row->id;
		
		//get branch
		$branch			= $this->_getBranch($row->user_id);

		//set branch
		$row->branch_id	= $branch->id;

		//set state
		$row->state		= $data['state']?$data['state']:1;

		$isnew 			= false;
		
		if ($isnew = !$row->id) 
		{
			$isnew = true;
			
			// Are we saving from an item edit?
			$row->created_by 	= (int) $user->get('id');
			
			$row->created		=date('Y-m-d H:i:s');
			
			if ($row->created && strlen(trim( $row->created )) <= 10) {
				$row->created 	.= ' 00:00:00';
			}
			
			$row->modified 		= $nullDate;
			$row->modified_by 	= 0;
			
		} 
		else 
		{
			
			$row->modified		= date('Y-m-d H:i:s');
			
			if ($row->modified && strlen(trim( $row->modified )) <= 10) {
				$row->modified 	.= ' 00:00:00';
			}
			
			$row->modified_by 	= (int) $user->id;

		}
		
		if($isnew)
		{
			//set invoice
			$tid				= ($this->_getInvoice($branch->id) + 1);
			$arrDate			= explode(' ',$row->created);
			$arrYear			= str_split($arrDate[0],2);
			$date				= explode('-', $arrDate[0]);
			
			//set invoice
			$row->transactionid	= (int) $tid;
			$row->invoice		= strtoupper(substr($branch->name,0,1).$date[1].$date[2].sprintf('%03d',$tid));
			
		}
		
		//get exchange rate
		$query = 'SELECT r.*, c.code '
				.' FROM #__ownservice_exchangerates AS r '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
				.' WHERE r.id='.$row->rate_id
				.' AND r.branch_id='.$row->branch_id
				;
		$this->_db->setQuery($query);
		$rate = $this->_db->loadObject();
		
		//set rate TH
		$row->rate_th	= $rate->rate_th;
		
		//set currency code
		$row->rate_code = $rate->code;

		//get fee
		$query = "SELECT fee FROM `#__ownservice_fees` "
					." WHERE `rate_id` =".$rate->id
					." AND `type` = 'fee' "
					." AND `from` <=".$row->amount
					." AND `to` >=".$row->amount
					;
		$this->_db->setQuery($query);
		$row->fee = $this->_db->loadResult();

		//get destination fee
		$query = "SELECT fee FROM `#__ownservice_fees` "
					." WHERE `rate_id` =".$rate->id
					." AND `type` = 'destinationfee' "
					." AND `from` <=".$row->amount
					." AND `to` >=".$row->amount
					;
		$this->_db->setQuery($query);
		$row->bankfee = $this->_db->loadResult();
		
		//set total
		$row->total		= round((($row->amount * ($row->rate + $row->extrarate))- ($row->bankfee + $row->extrabfee)), 2);

		//set thai total
		$row->total_th	= round(($row->amount * $row->rate_th), 2);

		// Make sure the data is valid
		if (!$row->check()) {
			$this->setError($row->getError() );
			return false;
		}

		// Store it in the db
		if (!$row->store()) {
			$this->setError( $this->_db->getErrorMsg() );
			return false;
		}

		//assign point 
		if($isnew)
		{
			OwnserviceHelper::updatePoint($row->id, $row->user_id, $row->amount);
		}

		$this->_data = $row;
		
		if(isset($data['occupation']))
		{
			//update occupation of member
			$query = 'UPDATE #__ownservice_users SET `occupation`='.$this->_db->Quote($data['occupation']).' WHERE user_id='.$row->user_id;
			$this->_db->setQuery($query);
			$this->_db->query();
		}
		
		if(isset($data['birthday']))
		{
			//update Birthday of Receiver Account
			$query = 'UPDATE #__ownservice_accounts SET `birthday`='.$this->_db->Quote($data['birthday']).' WHERE account_no='.$this->_db->Quote($row->account);
			$this->_db->setQuery($query);
			$this->_db->query();
		}
		
		return true;
	}

	/**
	 * Method get invoice for transaction
	 *
	 *@access private
	 *@since 1.5
	*/
	function _getInvoice($branchid)
	{
		$date1 = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
		$date2 = mktime(0, 0, 0, date('m'), date('d')+1, date('Y'));

		$where = ' WHERE ( created >= '
				.$this->_db->Quote(date('Y-m-d', $date1)." 04:00:00").' AND created <= '
				.$this->_db->Quote(date('Y-m-d', $date2)." 04:00:00").' )';

		$where .= ' AND branch_id='.$branchid;
		
		$query = ' SELECT MAX(transactionid) FROM #__ownservice_transactions '
				.$where;

		$this->_db->setQuery($query);
		$lastid	= $this->_db->loadResult();
		
		return $lastid;
	}

	/**
	 * Method get branch for transaction
	 *
	 *@access private
	 *@since 1.5
	*/
	function _getBranch($uid)
	{
		$query = ' SELECT branch_id '
				.' FROM #__ownservice_users '
				.' WHERE user_id='.$uid;
		
		$this->_db->setQuery($query);
		$branch_id =$this->_db->loadResult();

		if($branch_id)
		{
			$query = ' SELECT * '
					.' FROM #__ownservice_branches '
					.' WHERE id='.$branch_id;

			$this->_db->setQuery($query);
			$branch = $this->_db->loadObject();

			if(is_object($branch))
			{
				$branchname = $branch->name;

				if($branch->agent_id)
				{
					$query = ' SELECT * '
					.' FROM #__ownservice_branches '
					.' WHERE id='.$branch->agent_id;

					$this->_db->setQuery($query);
					$agent = $this->_db->loadObject();

					if(is_object($agent))
					{
						//$branchname = $agent->name;
						$branch = $agent;
					}
				}
			}
		}

		return $branch;
	}

	function getUsers() {

		//Initialise variables
		$user =& JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		// get vars
		$gid		= $user->gid;
		$level		= $ownuser->get('level_id');

		$where = array();

		$where[] = 'u.block=0';
		$where[] = 'p.group_id = 1';
		
		if($gid < 25) {

			$canView	= Ownservice::checkAccess('transaction','view','transaction','all');
			$canViewOwn = Ownservice::checkAccess('transaction','view','transaction','own');
		
			if($canViewOwn){
				$where[] = ' u.id='.$user->get('id');
			}

			if($canView) {
				if($level > 1 && $level < 5) {
					$where[] = ' p.branch_id ='.$ownuser->get('branch_id');
				}
			}
		}
	
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query = 'SELECT u.id, u.name, u.username '
				.' FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' LEFT JOIN #__ownservice_groups AS g ON g.id=p.group_id '
				.$where
				.' ORDER BY u.name';

		$this->_db->setQuery($query);
		$users = $this->_db->loadObjectList();

		return $users;
	}

	function getAccount($account)
	{	
		$query = 'SELECT * FROM #__ownservice_accounts WHERE account_no='.$this->_db->Quote($account);
		
		$this->_db->setQuery($query);
		$row = $this->_db->loadObject();
		
		return $row;
	}
	
	function getAccounts($id=0) {

		//Initialise variables
		$user		=& JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		// get vars
		$gid		= $user->gid;
		$level		= $ownuser->get('level_id');

		$where = array();
		$where[] = 'u.block=0';
		$where[] = 'a.deleted=0';

		if($gid < 25) {
			$canView	= Ownservice::checkAccess('transaction','view','transaction','all');
			$canViewOwn = Ownservice::checkAccess('transaction','view','transaction','own');
		
			if($canViewOwn){
				$where[] = ' a.user_id='.$user->get('id');
			} else {
				if($id > 0) {
					$where[] = 'a.user_id='.$id;
				} else {
					$where[] = 'a.user_id=0';
				}
			}

			if($canView) {
				if($level > 1 && $level < 5) {
					$where[] = ' p.branch_id ='.$ownuser->get('branch_id');
				}
			}
		} else {

			if($id > 0) {
				$where[] = 'a.user_id='.$id;
			} else {
				$where[] = 'a.user_id=0';
			}
		}

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query = ' SELECT a.* FROM #__ownservice_accounts AS a '
				.' LEFT JOIN #__users AS u ON u.id=a.user_id '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id=u.id '
				.$where
				.' ORDER BY a.account_no';

		$this->_db->setQuery($query);
		$accounts = $this->_db->loadObjectList();

		return $accounts;
	}

	/**
	 * Method get user
	 *
	 * @return object
	 * @since 1.5
	*/
	function getUser($id) {

		$query = ' SELECT p.*, u.name FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' WHERE u.id='.$id.' AND u.block = 0 ';
		$this->_db->setQuery($query);
		$profile = $this->_db->loadObject();
		
		if($profile->id > 0){
			$query = ' SELECT COUNT(id) FROM #__ownservice_files WHERE userid='.$profile->user_id;
			$this->_db->setQuery($query);
			$profile->fileid = $this->_db->loadResult();
		}

		return $profile;
	}

	/**
	 * Method get user receiving transfer
	 *
	 * @return object
	 * @since 1.5
	*/
	function getReceiving($branchid=0) {

		$where = '';
		if($branchid)
			$where = ' AND p.branch_id ='.$branchid;

		$query = ' SELECT p.user_id AS id ,u.name FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' WHERE  u.block = 0 AND p.deleted = 0 AND p.group_id=3 '.$where;
		$this->_db->setQuery($query);
		$users = $this->_db->loadObjectList();

		return $users;
	}

	/**
	 * Method to check expire archives
	 *
	 * @access public
	 * @since 1.5
	*/
	function canTransfer($uid) {
		//Initialise variables
		$user		=& JFactory::getUser();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
		//$jnow		=& JFactory::getDate($dateAndTime, 10);
		//$now		= $jnow->toMySQL();
		$now			=date('Y-m-d H:i:s');

		$query = ' SELECT p.* FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' WHERE p.user_id='.$uid
				.' AND deleted = 0 '
				.' AND expire_date >='.$this->_db->Quote($now);

		$this->_db->setQuery( $query );
		$row = $this->_db->loadObject();

		if(count($row) <= 0) {
			return false;
		}

		return true;
	}

	/**
	 * Method to fetch members according to a given mask
	 * 
	 * @return object
	 * @since 1.0
	 */
	function getmembers($mask="")
	{
		$user			=&JFactory::getUser();
		$ownuser	=& Ownservice::getUser();
	
		$where		=array();
		$where[]		='u.block = 0';
		
		if($ownuser->get('branch_id')){
			$where[]	='p.branch_id='.$ownuser->get('branch_id');
		} else {
			//check level 
			if( ($user->gid < 25) && ($ownuser->get('level_id') < 5) ){
				$where[]	='p.branch_id= 0';
			}
		}
		
		if($mask!="")
		{
			$where[] = "(u.name like '%$mask%' OR u.username like '%$mask%')";
		}
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );
		
		$query = 'SELECT u.* FROM #__users AS u '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id=u.id '
				.$where
				.' GROUP BY u.id '
				.' ORDER BY u.name';

		$this->_db->setQuery($query);
		$users = $this->_db->loadObjectlist();
		
		return $users;
	}
	
	/**
	 * Method to get currency list
	 * 
	 * @access public
	 * @return object array
	 * @since 1.5
	 */
	 function getCurrencies($branch_id)
	 {
	 	$user 		=& JFactory::getUser();
		$ownuser	=& Ownservice::getUser();
		
		$query = ' SELECT r.id, c.name '
				.' FROM #__ownservice_exchangerates AS r '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
				.' WHERE r.branch_id='.(int) $branch_id
				.' GROUP BY c.id '
				;
			  
		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();
		
		return is_array($rows)?$rows:array();
	 }
	 
	 /**
	  * Method to get image
	  * 
	  * @access public
	  * @return object
	  * @since 1.5
	  */
	  function getImage($uid)
	  {
	  	$query = ' SELECT * FROM #__ownservice_files '
				.' WHERE userid='.$uid
				.' GROUP BY id '
				.' ORDER BY id DESC ';
				
		$this->_db->setQuery($query);
		$row = $this->_db->loadObject();
		
		return $row;
	  }
	  
	  /**
	   * Method to transaction read  Cancelled
	   * 
	   * @return void
	   * @since 1.0
	   */
	  function readCancelled($id)
	  {
	  	$user 		=& JFactory::getUser();
		$ownuser	=& Ownservice::getUser();
		
		$where		=' WHERE id='.$id;
		
		//check access 
		if($user->gid < 25) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where .= ' AND branch_id ='.$ownuser->get('branch_id');
			}
		}
		
		$query 		= 'UPDATE #__ownservice_transactions SET iscancel=1 '.$where;
		$this->_db->setQuery($query);
		//echo $this->_db->_sql;
		if(!$this->_db->query()){
			$this->setError($this->_db->getErrorMsg());
			return false;
		}
		
		return true;
	  }

	/**
	 * Approval customer transfer
	*/
	function customerapprove(&$cid)
	{
		$table = & $this->getTable( 'ownservice_customertransaction','' );
		// Iterate the items to approve each one.
		foreach ($cid as $i => $id)
		{
			if ($table->load($id))
			{
				$data = array();
				$data['id'] 			= 0;
				$data['user_id'] 		= $table->user_id;
				$data['account'] 		= $table->account;
				$data['rate_id'] 		= $table->rate_id;
				$data['amount']  		= $table->amount;
				$data['rate'] 	 		= $table->rate;
				$data['extrarate']		= $table->extrarate;
				$data['bankfee'] 		= $table->bankfee;
				$data['extrabfee'] 		= $table->extrabfee;
				$data['reason'] 		= $table->reason;
				$data['reason_other'] 	= $table->reason_other;

				if(!$this->store($data)){
					return false;
				}
				
				//Delete customer transfer
				$this->deleteCustomerTransfer($id);
			}
			else
			{
				$this->setError($table->getError());
				return false;
			}
		}

		return true;
	 }

	 /**
	  * Delete customer transfer
	 */
	 function customerdelete(&$cid)
	 {
	 	$table = & $this->getTable( 'ownservice_customertransaction','' );
		// Iterate the items to approve each one.
		foreach ($cid as $i => $id)
		{
			if ($table->load($id))
			{
				if(!$this->deleteCustomerTransfer($id)) {
					return false;
				}
			}
		}

		return true;
	 }

	 /**
	  * Delete transfer of customer
	 */
	 function deleteCustomerTransfer($id)
	 {
	 	$query = 'DELETE FROM `#__ownservice_customer_transactions` WHERE `id`='.(int)$id;
	 	$this->_db->setQuery($query);
	 	if(!$this->_db->query()) {
	 		$this->setError($this->_db->getErrorMsg());
	 		return false;
	 	}

	 	return true;
	 }

	 function getCustomerTransfer()
	 {
	 	$cid = JRequest::getVar('cid',  0, '', 'array');
	 	$id  = $cid[0];

	 	$query = ' SELECT t.*, u.name, u.username '
	 			.' FROM `#__ownservice_customer_transactions` AS t '
	 			.' LEFT JOIN `#__users` AS u ON u.id = t.user_id '
	 			.' WHERE t.id = '.(int) $id;

		$this->_db->setQuery($query);
		$row = $this->_db->loadObject(); 

		return $row;
	 }

	 /**
	  * Save transfer of customer
	 */
	 function customertransfersave($data)
	 {
	 	//Check the request forgeries
		JRequest::checkToken() or jexit('Invalid Token');

		$user = & JFactory::getUser();

		$row  = & $this->getTable( 'ownservice_customertransaction','' );

		if(!$row->bind($data)) {
			$this->setError( $this->_db->getErrorMsg() );
			return false;
		}

		//get exchange rate
		$query = 'SELECT r.*'
				.' FROM #__ownservice_exchangerates AS r '
				.' WHERE r.id='.$row->rate_id
				.' AND r.branch_id='.$row->branch_id
				;
		$this->_db->setQuery($query);
		$rate = $this->_db->loadObject();
		
		//set rate TH
		$row->rate_th	= $rate->rate_th;
		
		//set total
		$row->total		= round((($row->amount * ($row->rate + $row->extrarate))- ($row->bankfee + $row->extrabfee)), 2);

		//set thai total
		$row->total_th	= round(($row->amount * $row->rate_th), 2);

		// Make sure the data is valid
		if (!$row->check()) {
			$this->setError($row->getError() );
			return false;
		}

		// Store it in the db
		if (!$row->store()) {
			$this->setError( $this->_db->getErrorMsg() );
			return false;
		}

		return true;
	 }
}